package com.qingmu.club.minaapi.custom.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qingmu.club.minaapi.plus.entity.ClubStaffScore;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface CustomClubStaffScoreMapper extends BaseMapper<ClubStaffScore> {
  //学习成绩排行
  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar, SUM(c.exam_score) scoreSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 1  GROUP BY c.staff_name) b ORDER BY b.scoreSum DESC")
  List<Map<String, Object>> sumScoreRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar, SUM(c.exam_score) scoreSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 1 AND (date_format(c.create_time,'%Y')=#{params.create_time}) GROUP BY c.staff_name) b ORDER BY b.scoreSum DESC")
  List<Map<String, Object>> yearScoreRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar, SUM(c.exam_score) scoreSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 1 AND (c.create_time BETWEEN  #{params.startDate} AND  #{params.endDate}) GROUP BY c.staff_name) b ORDER BY b.scoreSum DESC")
  List<Map<String, Object>> quarterScoreRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar, SUM(c.exam_score) scoreSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 1 AND (date_format(c.create_time,'%Y-%m')=#{params.create_time}) GROUP BY c.staff_name) b ORDER BY b.scoreSum DESC")
  List<Map<String, Object>> monthScoreRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar, SUM(c.exam_score) scoreSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 1 AND (c.create_time BETWEEN  #{params.startDate} AND  #{params.endDate}) GROUP BY c.staff_name) b ORDER BY b.scoreSum DESC")
  List<Map<String, Object>> weekScoreRankingList(@Param("params") Map<String, Object> params);

  //学习工时排行
  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar,sum(c.study_hours) hoursSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 0  GROUP BY c.staff_name) b ORDER BY b.hoursSum DESC")
  List<Map<String, Object>> sumHoursRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar,sum(c.study_hours) hoursSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 0 AND (date_format(c.create_time,'%Y')=#{params.create_time}) GROUP BY c.staff_name) b ORDER BY b.hoursSum DESC")
  List<Map<String, Object>> yearHoursRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar,sum(c.study_hours) hoursSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 0 AND (c.create_time BETWEEN  #{params.startDate} AND  #{params.endDate}) GROUP BY c.staff_name) b ORDER BY b.hoursSum DESC")
  List<Map<String, Object>> quarterHoursRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar,sum(c.study_hours) hoursSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 0  AND (date_format(c.create_time,'%Y-%m')=#{params.create_time}) GROUP BY c.staff_name) b ORDER BY b.hoursSum DESC")
  List<Map<String, Object>> monthHoursRankingList(@Param("params") Map<String, Object> params);

  @Select("SELECT * FROM (SELECT c.staff_name staffName,c.staff_avatar avatar,sum(c.study_hours) hoursSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} AND c.type = 0 AND (c.create_time BETWEEN  #{params.startDate} AND  #{params.endDate}) GROUP BY c.staff_name) b ORDER BY b.hoursSum DESC")
  List<Map<String, Object>> weekHoursRankingList(@Param("params") Map<String, Object> params);

  /**
   * 用户得分名次
   * @param params
   * @return
   */
  @Select("SELECT a.*,COUNT(0) rank FROM (SELECT c.staff_name staffName,c.staff_avatar avatar,c.staff_id staffId, SUM(c.exam_score) scoreSum FROM club_staff_score c WHERE c.club_id = #{params.club_id}  GROUP BY c.staff_name)a ,(SELECT c.staff_name staffName,c.staff_avatar avatar,c.staff_id staffId, SUM(c.exam_score) scoreSum FROM club_staff_score c WHERE c.club_id = #{params.club_id} GROUP BY c.staff_name)b WHERE a.scoreSum<=b.scoreSum  AND  a.staffId=#{params.staff_id} GROUP BY a.staffName ORDER BY a.scoreSum DESC")
  Map<String, Object> staffScoreRank(@Param("params") Map<String, Object> params);


  /**
   * 统计教练考评总分
   * @param params
   * @return
   */
  @Select("SELECT staff_name staffName,staff_avatar avatar, SUM(exam_score) scoreSum FROM club_staff_score   WHERE  staff_id=#{params.staff_id} AND type=1 AND grow_college_id=#{params.grow_college_id}")
  Map<String, Object> staffScoreSum(@Param("params") Map<String, Object> params);


}
